package org.qdao.implement.mysql;

import java.io.InputStream;
import java.io.Reader;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.net.MalformedURLException;
import java.net.URI;
import java.net.URL;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

import org.qdao.IDBEngine;
import org.qdao.IEntityDao;
import org.qdao.IFetchHandler;
import org.qdao.TableDescriptor;
import org.qdao.annotation.Column;
import org.qdao.annotation.PrimaryKey;
import org.qdao.annotation.Table;
import org.qdao.util.SQLTigger;

import com.tan.bean.User;
import com.tan.util.TanUtil;

/**
 * 
 * @author 譚元吉
 * @since 2010/02/06 11:00:50
 */
// @SuppressWarnings("")
public class MySqlEntityDao<T> implements InvocationHandler, IEntityDao<T> {
	
	/**
	 * DB 引擎 
	 */
	private IDBEngine dbEngine;

	/**
	 * 预处理
	 */
	private PreparedStatement pstmt;
	
	/**
	 * JDBC 结果集 
	 */
	private ResultSet rs;
	
	/**
	 * 数据库中对应的实体
	 */
	private Class<?> entity;
	
	/**
	 * 日志的记录
	 */
	private Logger logger = Logger.getLogger("MySqlEntityDaoLogger");
	
	/**
	 * SQL 语句 
	 */
	private StringBuffer sql = new StringBuffer();
	
	private Map<Class<?>, String> sqlCache = new HashMap<Class<?>, String>();
	
	
	
	/**
	 * 控制是否记录日志.
	 */
	private boolean isLogger = true;
	
	public void setLogger(boolean isLogger) {
		this.isLogger = isLogger;
	}

	public MySqlEntityDao(IDBEngine dbEngine, Class<?> entity) {
		this.dbEngine = dbEngine;
		this.entity = entity;
	}

	public MySqlEntityDao(IDBEngine dbEngine, Class<?> entity, boolean isLogger) {
		this.dbEngine = dbEngine;
		this.entity = entity;
		this.isLogger = isLogger;
	}

	public IDBEngine getDBEngine() {
		return this.dbEngine;
	}

	
	public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
		Object result = method.invoke(this, args);

//		logSql();
		logSql(method,args);

		close();
		return result;
	}

	public Integer count() {
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		int result = -1;
		sql.append("SELECT COUNT(*) FROM ").append(table.name);
		try {
			prepare();
			rs = pstmt.executeQuery();
			if (rs.next()) {
				result = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return result;
	}

	
	public Integer count(String sqlPart) {
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		int result = -1;
		sql.append("SELECT COUNT(*) FROM ").append(table.name).append(" WHERE 1 = 1 AND ").append(sqlPart);
		try {
			pstmt = getDBEngine().getConnection().prepareStatement(sql.toString());
			rs = pstmt.executeQuery();
			if (rs.next()) {
				result = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return result;
	}

	
	public boolean create() {
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		sql.append(SQLTigger.getMySqlCreateTigger(table));
		try {
			pstmt = getDBEngine().getConnection().prepareStatement(sql.toString());
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (InstantiationException e) {
			e.printStackTrace();
			return false;
		} catch (IllegalAccessException e) {
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		}
	}

	
	public boolean delete(T key) {
		Class<?> clazz = key.getClass();
		if (clazz == String.class) {
			return delete0(key.toString());
		}
		if (entity != clazz) {
			// logWarning("Your data is wrong!");
			logWarning("数据部匹配!");
			return false;
		}
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		sql.append("DELETE FROM ").append(table.name).append(" WHERE ");
		for (int i = 0; i < table.primaryKeys.length; i++) {
			if (i != table.primaryKeys.length - 1) {
				sql.append(table.primaryKeys[i]).append(" = ? AND ");
			} else
				sql.append(table.primaryKeys[i]).append(" = ?");
		}
		// prepare the statement
		try {
			prepare();
		} catch (SQLException e1) {
			e1.printStackTrace();
			return false;
		} catch (InstantiationException e1) {
			e1.printStackTrace();
			return false;
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
			return false;
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
			return false;
		}
		Field[] fs = clazz.getDeclaredFields();
		int count = 0;
		for (int i = 0; i < fs.length; i++) {
			if (fs[i].isAnnotationPresent(PrimaryKey.class)) {
				try {
					Object value = fs[i].get(key);
					setValue(pstmt, count + 1, fs[i], value, table);
					count++;
				} catch (SQLException e) {
					e.printStackTrace();
					return false;
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
					return false;
				} catch (IllegalAccessException e) {
					e.printStackTrace();
					return false;
				}
			}
		}
		try {
			int row = pstmt.executeUpdate();
			if (row <= 0)
				return false;
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	
	public boolean deletePart(String sqlPart) {
		return delete0(sqlPart);
	}

	private boolean delete0(String sqlPart) {
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		int result = -1;
		sql.append("DELETE FROM ").append(table.name).append(" WHERE ").append(sqlPart);
		try {
			pstmt = getDBEngine().getConnection().prepareStatement(sql.toString());
			result = pstmt.executeUpdate();
			if (result == -1) {
				return false;
			}
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return false;
	}

	
	public boolean delete(List<T> datas) {
		Iterator<?> iter = datas.iterator();
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		Field[] fs = entity.getDeclaredFields();
		if (fs.length != table.columns.length) {
			// logger.log(Level.WARNING,
			// "The object's fields can not match the table's columns!");
			logWarning(" 插入的数据项与表中的项不匹配");
			return false;
		}
		sql.append("DELETE FROM ").append(table.name).append(" WHERE ");
		for (int i = 0; i < table.primaryKeys.length; i++) {
			if (i != table.primaryKeys.length - 1) {
				sql.append(table.primaryKeys[i]).append(" = ? AND ");
			} else
				sql.append(table.primaryKeys[i]).append(" = ?");
		}
		try {
			prepare();
		} catch (SQLException e1) {
			e1.printStackTrace();
			return false;
		} catch (InstantiationException e1) {
			e1.printStackTrace();
			return false;
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
			return false;
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
			return false;
		}
		while (iter.hasNext()) {
			Object o = iter.next();
			Class<?> clazz = o.getClass();
			Field[] fields = clazz.getDeclaredFields();
			int count = 0;
			for (int i = 0; i < fields.length; i++) {
				if (fs[i].isAnnotationPresent(PrimaryKey.class)) {
					try {
						Object value = fs[i].get(o);
						setValue(pstmt, count + 1, fs[i], value, table);
						count++;
					} catch (SQLException e) {
						e.printStackTrace();
						return false;
					} catch (IllegalArgumentException e) {
						e.printStackTrace();
						return false;
					} catch (IllegalAccessException e) {
						e.printStackTrace();
						return false;
					}
				}
			}
			try {
				pstmt.addBatch();
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
		}
		try {
			int[] results = pstmt.executeBatch();
			pstmt.clearBatch();
			for (int i : results) {
				if (i < 0) {
					return false;
				}
			}
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	
	public boolean drop() {
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		sql.append("DROP TABLE ").append(table.name);
		try {
			prepare();
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (InstantiationException e) {
			e.printStackTrace();
			return false;
		} catch (IllegalAccessException e) {
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		}
	}

	
	public boolean drop(boolean cascade) {
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		if (cascade) {
			sql.append("DROP TABLE ").append(table.name).append(" CASCADE CONSTRAINTS");
		} else {
			sql.append("DROP TABLE ").append(table.name);
		}
		try {
			prepare();
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (InstantiationException e) {
			e.printStackTrace();
			return false;
		} catch (IllegalAccessException e) {
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		}
	}

	
	public boolean insert(T dto) {
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		boolean inserted = false;
		createInsertTigger(dto);
		try {
			prepare();
			prepareStatement(table, dto);
			pstmt.executeUpdate();
			inserted = true;
		} catch (SQLException e) {
			e.printStackTrace();
			inserted = false;
		} catch (InstantiationException e) {
			e.printStackTrace();
			inserted = false;
		} catch (IllegalAccessException e) {
			e.printStackTrace();
			inserted = false;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			inserted = false;
		}
		return inserted;
	}

	
	public boolean insert(List<?> dtos) {
		Iterator<?> iter = dtos.iterator();
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		Field[] fs = entity.getDeclaredFields();
		if (fs.length != table.columns.length) {
			// logger.log(Level.WARNING,
			// "The object's fields can not match the table's columns!");
			logWarning(" 插入的数据项与表中的项不匹配");
			return false;
		}
		createInsertTigger(table);
		try {
			prepare();
		} catch (SQLException e1) {
			e1.printStackTrace();
			return false;
		} catch (InstantiationException e1) {
			e1.printStackTrace();
			return false;
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
			return false;
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
			return false;
		}
		while (iter.hasNext()) {
			Object o = iter.next();
			try {
				prepareStatement(table, o);
				pstmt.addBatch();
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
				return false;
			} catch (IllegalAccessException e) {
				e.printStackTrace();
				return false;
			}
		}
		try {
			int[] results = pstmt.executeBatch();
			pstmt.clearBatch();
			for (int i : results) {
				if (i < 0) {
					return false;
				}
			}
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	private void logWarning(String message) {
		logger.log(Level.WARNING, message);
	}

	
	public boolean exist() {
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		sql.append("select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = '")
		.append(dbEngine.getConfig().getSchema())
		.append("' and TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = '").append(table.name).append('\'');
		String result = null;
		try {
			prepare();
			rs = pstmt.executeQuery();
			
			if (rs.next()) {
			    result = rs.getString("TABLE_NAME");
			}
		} catch (SQLException e) {
			int errorCode = e.getErrorCode();
			if (errorCode == 942) {
				logInfo("Table or View is not exist!");
			}
			return false;
		} catch (InstantiationException e) {
			e.printStackTrace();
			return false;
		} catch (IllegalAccessException e) {
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		}
		return null != result && result.equalsIgnoreCase(table.name);
	}

	
	public boolean lock(boolean share) {
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		if (share)
			sql.append("LOCK TABLE ").append(table.name).append(" IN SHARE MODE");
		else
			sql.append("LOCK TABLE ").append(table.name).append(" IN EXCLUSIVE MODE");
		try {
			prepare();
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			return false;
		} catch (InstantiationException e) {
			e.printStackTrace();
			return false;
		} catch (IllegalAccessException e) {
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		}
	}

	
	public boolean lock(int timeout) {
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		if (timeout <= 0)
			sql.append("SELECT * FROM ").append(table.name).append(" FOR UPDATE");
		else
			sql.append("SELECT * FROM ").append(table.name).append(" FOR UPDATE " + timeout);
		try {
			prepare();
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			return false;
		} catch (InstantiationException e) {
			e.printStackTrace();
			return false;
		} catch (IllegalAccessException e) {
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		}
	}

	
	public T lock(T key) {
		Class<?> clazz = key.getClass();
		if (entity != clazz) {
			// logWarning("Your data is wrong!");
			logWarning("数据不匹配!");
		}
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		sql.append("SELECT * FROM ").append(table.name).append(" WHERE ");
		for (int i = 0; i < table.primaryKeys.length; i++) {
			if (i != table.primaryKeys.length - 1) {
				sql.append(table.primaryKeys[i]).append(" = ? AND ");
			} else
				sql.append(table.primaryKeys[i]).append(" = ?");
		}
		sql.append(" FOR UPDATE NOWAIT");
		// prepare the statement
		try {
			prepare();
		} catch (SQLException e1) {
			e1.printStackTrace();
		} catch (InstantiationException e1) {
			e1.printStackTrace();
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}

		Field[] fs = clazz.getDeclaredFields();
		int count = 0;
		for (int i = 0; i < fs.length; i++) {
			if (fs[i].isAnnotationPresent(PrimaryKey.class)) {
				try {
					Object value = fs[i].get(key);
					setValue(pstmt, count + 1, fs[i], value, table);
					count++;
				} catch (SQLException e) {
					e.printStackTrace();
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				}
			}
		}
		try {
			rs = pstmt.executeQuery();
			if (rs == null) {
				return null;
			}
			while (rs.next()) {
				Field[] fields = key.getClass().getFields();
				for (int i = 0; i < fields.length; i++) {
					if (fields[i].getType() == rs.getObject(i + 1).getClass()) {
						fields[i].set(key, rs.getObject(i + 1));
					}
				}
				break;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return key;
	}

	
	public T lock(T key, int timeout) {
		Class<?> clazz = key.getClass();
		if (entity != clazz) {
			// logWarning("Your data is wrong!");
			logWarning("数据不匹配!");
		}
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		sql.append("SELECT * FROM ").append(table.name).append(" WHERE ");
		for (int i = 0; i < table.primaryKeys.length; i++) {
			if (i != table.primaryKeys.length - 1) {
				sql.append(table.primaryKeys[i]).append(" = ? AND ");
			} else
				sql.append(table.primaryKeys[i]).append(" = ?");
		}
		// append the timeout
		if (timeout <= 0)
			sql.append(" FOR UPDATE");
		else
			sql.append(" FOR UPDATE " + timeout);
		// prepare the statement
		try {
			prepare();
		} catch (SQLException e1) {
			e1.printStackTrace();
		} catch (InstantiationException e1) {
			e1.printStackTrace();
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}

		Field[] fs = clazz.getDeclaredFields();
		int count = 0;
		for (int i = 0; i < fs.length; i++) {
			if (fs[i].isAnnotationPresent(PrimaryKey.class)) {
				try {
					Object value = fs[i].get(key);
					setValue(pstmt, count + 1, fs[i], value, table);
					count++;
				} catch (SQLException e) {
					e.printStackTrace();
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				}
			}
		}
		try {
			rs = pstmt.executeQuery();
			if (rs == null) {
				return null;
			}
			while (rs.next()) {
				Field[] fields = key.getClass().getFields();
				for (int i = 0; i < fields.length; i++) {
					if (fields[i].getType() == rs.getObject(i + 1).getClass()) {
						fields[i].set(key, rs.getObject(i + 1));
					}
				}
				break;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return key;
	}

	
	@SuppressWarnings("unchecked")
	public List<T> lock(String sqlPart) {
		List<T> list = new ArrayList<T>();
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		sql.append("SELECT * FROM ").append(table.name).append(" WHERE ")
				.append(sqlPart).append(" FOR UPDATE");
		// prepare the statement
		try {
			prepare();
		} catch (SQLException e1) {
			e1.printStackTrace();
		} catch (InstantiationException e1) {
			e1.printStackTrace();
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}

		try {
			rs = pstmt.executeQuery();
			if (rs == null) {
				return null;
			}
			while (rs.next()) {
				Object result = null;
				result = entity.newInstance();
				Field[] fields = result.getClass().getFields();
				for (int i = 0; i < fields.length; i++) {
					if (fields[i].getType() == rs.getObject(i + 1).getClass()) {
						fields[i].set(result, rs.getObject(i + 1));
					}
				}
				list.add((T) result);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		}
		return list;
	}

	
	public List<T> lock(String sqlPart, int timeout) {
		List<T> list = new ArrayList<T>();
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		sql.append("SELECT * FROM ").append(table.name).append(" WHERE ")
				.append(sqlPart).append(" FOR UPDATE ");

		// add the timeout
		if (timeout >= 0)
			sql.append(timeout);
		// prepare the statement
		try {
			prepare();
		} catch (SQLException e1) {
			e1.printStackTrace();
		} catch (InstantiationException e1) {
			e1.printStackTrace();
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}

		try {
			rs = pstmt.executeQuery();
			if (rs == null) {
				return null;
			}
			while (rs.next()) {
				Object result = null;
				result = entity.newInstance();
				Field[] fields = result.getClass().getFields();
				for (int i = 0; i < fields.length; i++) {
					if (fields[i].getType() == rs.getObject(i + 1).getClass()) {
						fields[i].set(result, rs.getObject(i + 1));
					}
				}
				list.add((T) result);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		}
		return list;
	}

	
	public boolean match(Class<? extends T> table) {
		if (table == entity) {
			return true;
		}
		return false;
	}

	
	public T select(T key) {
		Class<?> clazz = key.getClass();
		if (entity != clazz) {
			// logWarning("Your data is wrong!");
			logWarning("数据不匹配!");
		}
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		sql.append("SELECT * FROM ").append(table.name).append(" WHERE ");
		for (int i = 0; i < table.primaryKeys.length; i++) {
			if (i != table.primaryKeys.length - 1) {
				sql.append(table.primaryKeys[i]).append(" = ? AND ");
			} else
				sql.append(table.primaryKeys[i]).append(" = ?");
		}
		// prepare the statement
		try {
			prepare();
		} catch (SQLException e1) {
			e1.printStackTrace();
		} catch (InstantiationException e1) {
			e1.printStackTrace();
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}

		Field[] fs = clazz.getDeclaredFields();
		int count = 0;
		for (int i = 0; i < fs.length; i++) {
			if (fs[i].isAnnotationPresent(PrimaryKey.class)) {
				try {
					Object value = fs[i].get(key);
					setValue(pstmt, count + 1, fs[i], value, table);
					count++;
				} catch (SQLException e) {
					e.printStackTrace();
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				}
			}
		}
		try {
			rs = pstmt.executeQuery();
			if (rs == null) {
				return null;
			}
			if (rs.next()) {
				// 修改新方式设置值到key中. 2011-3-3 19:49
				Object value;
				Field field = null;
				for (int i = 0; i < table.columns.length; i++) {
					value = rs.getObject(table.columns[i].name);
					try {
						field = clazz.getDeclaredField(table.columns[i].javaField);
						if (!field.isAccessible()) {
							field.setAccessible(true);
						}
					} catch (SecurityException e) {
						e.printStackTrace();
					} catch (NoSuchFieldException e) {
						e.printStackTrace();
					}
					if (null != field && null != value) {
						field.set(key, value);
					}
				}
				/*Field[] fields = key.getClass().getFields();
				for (int i = 0; i < fields.length; i++) {
					if (fields[i].getType() == rs.getObject(i + 1).getClass()) {
						fields[i].set(key, rs.getObject(i + 1));
					}
				}
				*/
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return key;
	}

	
	public List<T> select() {
		List<T> list = new ArrayList<T>();
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		sql.append("SELECT * FROM ").append(table.name);
		try {
			prepare();
			rs = pstmt.executeQuery();
			if (rs == null) {
				return null;
			}
			while (rs.next()) {
				list.add(table2object(table));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return list;
	}

	
	public List<T> select(IFetchHandler<T> filter) {
		// TODO filter begin
		filter.begin();
		List<T> list = new ArrayList<T>();
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		sql.append("SELECT * FROM ").append(table.name);
		try {
			prepare();
			rs = pstmt.executeQuery();
			if (rs == null) {
				return null;
			}
			while (rs.next()) {
				Object result = null;
				result = entity.newInstance();
				Field[] fields = result.getClass().getFields();
				for (int i = 0; i < fields.length; i++) {
					fields[i].set(result, rs.getObject(i + 1));
				}
				list.add((T) result);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		// TODO filter end
		filter.end();
		return list;

	}

	
	@SuppressWarnings("unchecked")
	public List<T> select(String sqlPart) {
		if (null == sqlPart || sqlPart.trim().length() == 0) {
			return select();
		}
		sqlPart = sqlPart.toLowerCase();
		String sqlTrim = sqlPart.trim();
		List<T> list = new ArrayList<T>();
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		sql.append("SELECT * FROM ").append(table.name);
		if (sqlTrim.startsWith("limit")) {
			sql.append(' ' + sqlPart);
		} else if (sqlTrim.startsWith("order by"))  {
			sql.append(" ").append(sqlPart);
		} else {
			sql.append(" WHERE 1 = 1 AND ").append(sqlPart);
		}
		try {
			prepare();
			rs = pstmt.executeQuery();
			if (rs == null) {
				return null;
			}
			while (rs.next()) {
				list.add(table2object(table));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return list;
	}

	
	public List<T> select(String sqlPart, IFetchHandler<T> filter) {
		return null;
	}

	
	public T update(T key) {
		Class<?> clazz = key.getClass();
		if (entity != clazz) {
			// logWarning("Your data is wrong!");
			logWarning("数据不匹配!");
		}
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		// generate the update sql
		List<Object> setValues = createUpdateTigger(table, key);
		// prepare the statement
		try {
			prepare();
		} catch (SQLException e1) {
			e1.printStackTrace();
		} catch (InstantiationException e1) {
			e1.printStackTrace();
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
//		Object[] primaryKeys = new Object[table.primaryKeys.length];
//		Field[] fs = clazz.getDeclaredFields();
//		int count = 0;
//		for (int i = 0; i < fs.length; i++) {
//			if (fs[i].isAnnotationPresent(Column.class)) {
//				try {
//					Object value = fs[i].get(key);
//					if (fs[i].isAnnotationPresent(PrimaryKey.class)) {
//						primaryKeys[0] = value;
//					}
//					setValue(pstmt, count + 1, fs[i], value, table);
//					count++;
//				} catch (SQLException e) {
//					e.printStackTrace();
//				} catch (IllegalArgumentException e) {
//					e.printStackTrace();
//				} catch (IllegalAccessException e) {
//					e.printStackTrace();
//				}
//			}
//		}
//		for (int i = 0; i < fs.length; i++) {
//			if (fs[i].isAnnotationPresent(PrimaryKey.class)) {
//				try {
//					Object value = fs[i].get(key);
//					setValue(pstmt, count + 1, fs[i], value, table);
//					count++;
//				} catch (SQLException e) {
//					e.printStackTrace();
//				} catch (IllegalArgumentException e) {
//					e.printStackTrace();
//				} catch (IllegalAccessException e) {
//					e.printStackTrace();
//				}
//			}
//		}
		
		// set the update values.
		if (null != setValues) {
			int size = setValues.size();
			if (size > 0) {
				for (int i = 1; i <= size; i++) {
					try {
						pstmt.setObject(i, setValues.get(i - 1));
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}
		try {
			pstmt.executeUpdate();
			sql = new StringBuffer();
			return select(key);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		}
		return key;
	}

	private List<Object> createUpdateTigger(TableDescriptor table, T key) {
		sql.append("UPDATE ").append(table.name).append(" SET ");
		List<Object> setValues = new ArrayList<Object>();
		for (int i = 0; i < table.columns.length; i++) {
			Object value = getFieldValue(table.columns[i].javaField, key);
			if (value != null) {
				setValues.add(value);
				sql.append(table.columns[i].name)
				   .append(" = ?,");
			}
		}
		sql.deleteCharAt(sql.length() - 1);
		if (table.primaryKeys == null || table.primaryKeys.length == 0) {
			warn("主键为空" + table.name);
			return null;
		}
		sql.append(" WHERE ");
		for (int i = 0; i < table.primaryKeys.length; i++) {
			Object value = getPrimaryKeyValue(table.primaryKeys[i], key);
			setValues.add(value);
			sql.append(table.primaryKeys[i])
			.append((i != table.primaryKeys.length - 1) ? " = ? AND " : " = ?");
		}
		return setValues;
	}

	private Object getPrimaryKeyValue(final String primaryKey, final T key) {
		Field[] fields = entity.getDeclaredFields();
		if (null != fields && fields.length > 0) {
			for (int i = 0; i < fields.length; i++) {
				if (fields[i].isAnnotationPresent(Column.class) && fields[i].isAnnotationPresent(PrimaryKey.class)) {
					Column column = fields[i].getAnnotation(Column.class);
					if (column.name().equals(primaryKey)) {
						try {
							return fields[i].get(key);
						} catch (IllegalArgumentException e) {
							e.printStackTrace();
						} catch (IllegalAccessException e) {
							e.printStackTrace();
						}
					}
				}
			}
		}
		return null;
	}

	private Object getFieldValue(final String fieldName, T key) {
		Field field = null;
		Object value = null;
		try {
			field = entity.getDeclaredField(fieldName);
			if (!field.isAccessible()) {field.setAccessible(true);};
			 value = field.get(key);
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return value;
	}

	
	public boolean update(List<T> datas) {
		Iterator<?> iter = datas.iterator();
		Connection connection = null;
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		Field[] fs = entity.getDeclaredFields();
		if (fs.length != table.columns.length) {
			// logger.log(Level.WARNING,
			// "The object's fields can not match the table's columns!");
			logWarning(" 插入的数据项与表中的项不匹配");
			return false;
		}
		sql.append("UPDATE ").append(table.name).append(" SET ");
		for (int i = 0; i < table.columns.length; i++) {
			if (i != table.columns.length - 1) {
				sql.append(table.columns[i].name).append(" = ?,");
			} else
				sql.append(table.columns[i].name).append(" = ?");
		}
		try {
			prepare();
		} catch (SQLException e1) {
			e1.printStackTrace();
			return false;
		} catch (InstantiationException e1) {
			e1.printStackTrace();
			return false;
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
			return false;
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
			return false;
		}
		while (iter.hasNext()) {
			Object o = iter.next();
			Class<?> clazz = o.getClass();
			Field[] fields = clazz.getDeclaredFields();
			int count = 0;
			for (int i = 0; i < fields.length; i++) {
				if (fs[i].isAnnotationPresent(Column.class)) {
					try {
						Object value = fs[i].get(o);
						setValue(pstmt, count + 1, fs[i], value, table);
						count++;
					} catch (SQLException e) {
						e.printStackTrace();
						return false;
					} catch (IllegalArgumentException e) {
						e.printStackTrace();
						return false;
					} catch (IllegalAccessException e) {
						e.printStackTrace();
						return false;
					}
				}
			}
			try {
				pstmt.addBatch();
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
		}
		try {
			connection = pstmt.getConnection();
			int[] results = pstmt.executeBatch();
			connection.commit();
			pstmt.getConnection().commit();
			pstmt.clearBatch();
			for (int i : results) {
				if (i < 0) {
					return false;
				}
			}
			return true;
		} catch (SQLException e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			return false;
		}
	}

	public void close() {
		if (sql.length() != 0) {
			sql = new StringBuffer();
		}
		if (pstmt != null) {
			try {
				pstmt.close();
				pstmt = null;
//				log("PreparedStatement closed");
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (rs != null) {
			try {
				rs.close();
				rs = null;
//				log("ResultSet closed");
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (dbEngine != null) {
			dbEngine.dispose();
//			log("DBEngine disposed");
		}
	}

	/**
	 * 
	 * @author tan
	 * @param table
	 *            2010/02/26 9:40:06
	 * @param dto 
	 */
	private void createInsertTigger(T dto) {
		Class<?> clz = dto.getClass();
		if ( sqlCache.containsKey(clz) ) {
			// 是否存储?
			sql.append( sqlCache.get(clz) );
		} else {
			StringBuffer symbol = new StringBuffer();
			Table table = (Table) clz.getAnnotation(Table.class);
			if (null == table) { 
				throw new RuntimeException("DTO内不含有Table注解Annotation");
			}
			sql.append("INSERT INTO ").append(table.name()).append(" (");
			Field[] fields = clz.getDeclaredFields();
			Object value = null;
			Column column;
			for (int i = 0; i < fields.length; i++) {
				column = fields[i].getAnnotation(Column.class);
				if ( !fields[i].isAccessible() ) {
					fields[i].setAccessible( true );
				}
				try {
					value = fields[i].get(dto);
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				}
				if (null != value && column != null) {
					sql.append(column.name() + ',');
					symbol.append("?,");
				}
			}
			sql.deleteCharAt(sql.length() - 1);
			symbol.deleteCharAt(symbol.length() - 1);
			sql.append(") VALUES ( ").append(symbol).append(" )");
		}
		
	}
	
	public static void main(String[] args) {
		StringBuffer symbol = new StringBuffer();
		StringBuffer sql = new StringBuffer();
		
		User dto = new User();
		dto.id = 1;
		//dto.lastTime = new Date();
		dto.realname = "realname";
		dto.username = "username";
		
		Class clz = dto.getClass();
		Table table = (Table) clz.getAnnotation(Table.class);
		if (null == table) { 
			throw new RuntimeException("DTO内不含有Table注解Annotation");
		}
		sql.append("INSERT INTO ").append(table.name()).append(" (");
		Field[] fields = clz.getDeclaredFields();
		Object value = null;
		Column column;
		for (int i = 0; i < fields.length; i++) {
			column = fields[i].getAnnotation(Column.class);
			try {
				value = fields[i].get(dto);
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			}
			if (null != value && column != null) {
				sql.append(column.name() + ',');
				symbol.append("?,");
			}
		}
		sql.deleteCharAt(sql.length() - 1);
		symbol.deleteCharAt(symbol.length() - 1);
		sql.append(") VALUES ( ").append(symbol).append(" )");
		System.out.println(sql);
	
	}
	
	private void createInsertTigger(TableDescriptor table) {
		StringBuffer symbol = new StringBuffer();
		sql.append("INSERT INTO ").append(table.name).append(" (");
		for (int i = 0; i < table.columns.length; i++) {
			// TODO check id for the auto_increment
//			if (table.columns[i].name.toLowerCase().indexOf("id") != -1) {
//				continue;
//			}
			
			if (i == table.columns.length - 1) {
				sql.append(table.columns[i].name + ')');
				symbol.append('?');
			} else {
				sql.append(table.columns[i].name).append(',');
				symbol.append("?,");
			}
		}
		sql.append(" VALUES ( ").append(symbol).append(" )");
	}

	public List<T> selectNative(String nativeSql) {
		List<T> list = new ArrayList<T>();
		Class<?> clazz = null;
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		sql.append(nativeSql);
		// prepare the statement
		try {
			prepare();
		} catch (SQLException e1) {
			e1.printStackTrace();
		} catch (InstantiationException e1) {
			e1.printStackTrace();
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}

		try {
			rs = pstmt.executeQuery();
			// check for the result set
			if (rs == null || rs.wasNull()) {
				return null;
			}
			while (rs.next()) {
///////////////////////////////////////////////////////OLD code start///////////////////////////////////////////////////////.
//				Object result = entity.newInstance();
///*				Field[] fields = result.getClass().getFields();
//				for (int i = 0; i < fields.length; i++) {
//					// check result set for object's fields
//					Object v = rs.getObject(i + 1);
//					if(v == null) {
//						return null;
//					} else  {
//						fields[i].set(result, v);
//					}
//				}*/
//				
//				/**************** TODO 最新修改设置值 by QDao.
//				 * Modify By tyj 2011-04-30 23:40:00***********/				
//				int len = table.columns.length;
//				clazz = result.getClass();
//				for (int i = 0; i < len; i++) {
//					Field field = clazz.getField(table.columns[i].javaField);
//					if (null != field)  {
//						Object v = rs.getObject(table.columns[i].name);
//						field.set(result, v);
//					}
//				}
//				/**************** Modify By tyj 2011-04-30 23:40:00***********/				
//				list.add((T) result);
///////////////////////////////////////////////////////OLD code end ///////////////////////////////////////////////////////.
				list.add(table2object(table));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} 
		return list;
	}
	
	public List<T> selectNative(String nativeSql, boolean isReturnEntityList) {
		List result;
		if (isReturnEntityList) { 
			result = new ArrayList<T>();
		} else {
			result = new ArrayList();
		}
		try {
			sql.append(nativeSql);
			prepare();
			rs = pstmt.executeQuery();
			// check for the result set
			if (rs == null || rs.wasNull()) {
				return null;
			}
			if (isReturnEntityList) {
				while (rs.next()) {
					Object dto = null;
					dto = entity.newInstance();
					Field[] fields = dto.getClass().getFields();
					Object v;
					for (int i = 0; i < fields.length; i++) {
						// check result set for object's fields
						v = rs.getObject(i + 1);
						if(v == null) {
							//return null;
							fields[i].set( dto, null );
						} else  {
							fields[i].set(dto, v);
						}
					}
					((List<T>) result).add((T) dto);
				}
			} else {
				while (rs.next()) {
					result.add(rs.getObject(1));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return result;
	}
	
/*------------------------------------------------------------------------------*
  				私有方法区	
  				Create by Tanyuanji 2010-0424 23:09	
 *------------------------------------------------------------------------------*/
	/**
	 * <pre>
	 * 	prepare the statement , 
	 * 	set the value for the statement
	 * </pre>
	 * 
	 * @author tan
	 * @param table
	 *            the dto's table's descriptor
	 * @param dto
	 *            dto
	 * @throws IllegalAccessException
	 * @throws SQLException
	 *             2010/02/26 15:24:03
	 */
	private void prepareStatement(TableDescriptor table, Object dto) throws IllegalAccessException, SQLException {
		Class<?> clazz = dto.getClass();
		Field[] fs = clazz.getDeclaredFields();
		if (entity != clazz) {
			// logger.log(Level.WARNING,
			// "The object's data can not match the table!");
			logWarning(" 插入的数据不匹配");
			throw new SQLException("The object's data can not match the table!");
		}
		if (fs.length != table.columns.length) {
			logWarning(" 插入的数据项与表中的项不匹配");
			throw new SQLException("The object's fields can not match the table's columns!");
		}
		int count = -1;
		for (int i = 0; i < fs.length; i++) {
			// System.out.println(fs[i].getName() + "--> " +
			// fs[i].get(o) + " --> " + fs[i].getType());
			if ( !fs[i].isAccessible() ) {
				fs[i].setAccessible( true );
			}
			Object value = fs[i].get(dto);
			if (value == null &&
					!table.columns[i].nullable) {
				throw new SQLException("");
			} else if (value == null &&
					table.columns[i].nullable) {
				continue;
				//pstmt.setNull(i + 1, table.columns[i].type.value());
			}
			count++;
			setValue(pstmt, count + 1, fs[i], value, table);
		}
	}	
	
	private void setValue(PreparedStatement pstmt,
			int index, Field field, Object value, TableDescriptor table) throws SQLException {
		if (null == value) {
			return;
		}
		// set the value for PrepareStatement
		Class<?> type = field.getType();
		if (type == String.class) {
			pstmt.setString(index, (String) value);
		} else if (type == int.class
				|| type == Integer.class) {
			// TODO check the nullable
//			if (table.columns[index - 1].nullable && value == null) {
//				pstmt.setInt(index, table.columns[index - 1].decimal);
//			} else
				pstmt.setInt(index, (Integer) value);
		} else if (type  == java.util.Date.class) {
			pstmt.setTimestamp(index, new Timestamp(((java.util.Date) value).getTime()));
		}else if (type == float.class
				|| type == Float.class) {
			pstmt.setFloat(index, (Float) value);
		} else if (type == long.class
				|| type == Long.class) {
			pstmt.setLong(index, (Long) value);
		} else if (type == byte.class
				|| type == Byte.class) {
			pstmt.setByte(index, (Byte) value);
		} else if (type == BigDecimal.class) {
			pstmt.setBigDecimal(index, (BigDecimal) value);
		} else if (type == boolean.class
				|| type == Boolean.class) {
			if (null == value) {
				pstmt.setBoolean(index, false);
			}
			else {
				pstmt.setBoolean(index, (Boolean) value);
			}
		} else if (type == java.sql.Date.class) {
			pstmt.setDate(index, (java.sql.Date) value);
		} else if (type == Time.class) {
			pstmt.setTime(index, (Time) value);
		} else if (type == Timestamp.class) {
			pstmt.setTimestamp(index, (Timestamp) value);
		} else if (type == byte[].class ||
				type == Byte[].class) {
			pstmt.setBytes(index, (byte[]) value);
		} else if (InputStream.class.isAssignableFrom(type)) {
			pstmt.setBinaryStream(index, (InputStream) value);
		} else if (Reader.class.isAssignableFrom(type)) {
			pstmt.setCharacterStream(index, (Reader) value);
		} else if (Blob.class.isAssignableFrom(type)) {
			pstmt.setBlob(index, (Blob) value);
		} else if (Clob.class.isAssignableFrom(type)) {
			pstmt.setClob(index, (Clob) value);
		} else if (type == URL.class) {
			pstmt.setURL(index, (URL) value);
		} else if (type == URI.class || URI.class.isAssignableFrom(type)) {
			try {
				URL url = ((URI) value).toURL();
				pstmt.setURL(index, url);
			} catch (MalformedURLException e) {
				e.printStackTrace();
			}
		} else if (type == byte[].class ||
				type == Byte[].class) {
			pstmt.setBytes(index, (byte[]) value);
		}

		// String buf = sqlInfo.toString().replaceFirst("\\?",
		// value.toString());
		// sqlInfo = new StringBuilder(buf);
	}

	// private StringBuilder sqlInfo = new StringBuilder();

	@SuppressWarnings("unused")
	private void setValueBackup(PreparedStatement pstmt,
			int index, Field field, Object value, TableDescriptor table) throws SQLException {
		// set the value for PrepareStatement
		Class<?> type = field.getType();
		if (type == String.class) {
			pstmt.setString(index, (String) value);
		} else if (type == int.class
				|| type == Integer.class) {
			// TODO check the nullable
			if (table.columns[index - 1].nullable && value == null) {
				pstmt.setInt(index, table.columns[index - 1].decimal);
			} else
				pstmt.setInt(index, (Integer) value);
		} else if (type == float.class
				|| type == Float.class) {
			pstmt.setFloat(index, (Float) value);
		} else if (type == long.class
				|| type == Long.class) {
			pstmt.setLong(index, (Long) value);
		} else if (type == byte.class
				|| type == Byte.class) {
			pstmt.setByte(index, (Byte) value);
		} else if (type == BigDecimal.class) {
			pstmt.setBigDecimal(index, (BigDecimal) value);
		} else if (type == boolean.class
				|| type == Boolean.class) {
			pstmt.setBoolean(index, (Boolean) value);
		} else if (type == java.sql.Date.class) {
			pstmt.setDate(index, (java.sql.Date) value);
		} else if (type == Time.class) {
			pstmt.setTime(index, (Time) value);
		} else if (type == Timestamp.class) {
			pstmt.setTimestamp(index, (Timestamp) value);
		} else if (type == byte[].class ||
				type == Byte[].class) {
			pstmt.setBytes(index, (byte[]) value);
		} else if (InputStream.class.isAssignableFrom(type)) {
			pstmt.setBinaryStream(index, (InputStream) value);
		} else if (Reader.class.isAssignableFrom(type)) {
			pstmt.setCharacterStream(index, (Reader) value);
		} else if (Blob.class.isAssignableFrom(type)) {
			pstmt.setBlob(index, (Blob) value);
		} else if (Clob.class.isAssignableFrom(type)) {
			pstmt.setClob(index, (Clob) value);
		} else if (type == URL.class) {
			pstmt.setURL(index, (URL) value);
		} else if (type == URI.class || URI.class.isAssignableFrom(type)) {
			try {
				URL url = ((URI) value).toURL();
				pstmt.setURL(index, url);
			} catch (MalformedURLException e) {
				e.printStackTrace();
			}
		} else if (type == byte[].class ||
				type == Byte[].class) {
			pstmt.setBytes(index, (byte[]) value);
		}
	}

	/**
	 * prepare the statement
	 * 
	 * @author tan 2010/03/01 10:48:05
	 * @throws ClassNotFoundException
	 * @throws IllegalAccessException
	 * @throws InstantiationException
	 * @throws SQLException
	 */
	private void prepare() throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
		Connection conn = getDBEngine().getConnection();
		if ( conn == null ) {
			warn( "连接为空! conn == null" );
		} else if ( conn.isClosed() ) {
			
			warn( "连接关闭,重连接! conn.isClosed" );
			
			// reconnect
			conn = getDBEngine().getConnection();
		} else {
			pstmt = conn.prepareStatement( sql.toString() );
		}
	}
	
/*	*//**
	 * 记录sql语句
	 *//*
	@SuppressWarnings("unused")
	private void logSql() {
		// log the sql
		if (isLogger && sql != null && sql.toString().trim().length() != 0) {
			logger.log(Level.INFO, "{\r\n\t" + sql + ";\r\n}");
		}
	}*/

	private void log(String info) {
		// log the sql
		if (isLogger && info != null && info.trim().length() != 0) {
			logger.log(Level.INFO, "{\r\n\t" + info + ";\r\n}");
		}
	}
	
	/**
	 * 日志记录sql语句
	 * 格式化输出方法名
	 * 输出SQL语句
	 */
	private void logSql(Method method, Object ... args) {
		// log the sql
		if (isLogger && sql != null && sql.toString().trim().length() != 0) {
			StringBuffer buf = new StringBuffer();
			String arg = "";
			if (null == args || args.length == 0) {
				arg = Arrays.toString(args);
				if (null == arg || "null".equals(arg)) {
					arg = "";
				}
			}
			TanUtil.append(buf,
					"信息: ",
					method.getName(), " " , arg, "\r\nSQL: ", sql ,";\r\n"
					);
			echo(buf.toString());
//			logger.log(Level.INFO,  "{\r\nMETHOD'S NAME: " + method.getName() + "\r\nSQL: " + sql + ";\r\n}");
			method = null;
		}
	}
	
	
	@SuppressWarnings("unused")
	private void echo(String msg) {
		System.out.println(msg);
	}

	/**
	 * log the information
	 * 
	 * @author tan
	 * @param msg
	 * 
	 * 2010/02/06 10:58:14
	 */
	private void logInfo(String msg) {
		logger.log(Level.INFO, msg);
	}	
	
	private void warn(String msg) {
		logger.log(Level.WARNING, msg);
	}	
	
	
	public boolean connect() {
		if (connected) return true;
		else if (forceConnect()) {
			connected = true;
			return true;
		} else {
			TanUtil.startMysql();
			return true;
		}
	}
	
	private boolean connected = false;
	
	private boolean forceConnect() {
		// TODO 判断mysql是否能够连接上.
		Connection conn = null;
		try {
			conn = getDBEngine().getConnection();
			return conn != null;
		} catch (Exception e) {
		} 
		return false;
	}

	public T selectByPrimaryKey(Object... objects) {
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		int len = table.pks.length;
		if (objects.length != len){
			return null;
		}
		sql.append("SELECT * FROM ").append(table.name).append(" WHERE ");
		for (int i = 0; i < len; i++) {
			sql.append(i != 0 ? " AND " : "");
			if (objects[i].getClass().isAssignableFrom(Number.class)) {
				sql.append(table.pks[i].name).append(" = ").append(objects[i]);
			}  else {
				sql.append(table.pks[i].name).append(" = ").append('\'').append(objects[i]).append('\'');
			}
		}
		try {
			prepare();
			rs = pstmt.executeQuery();
			if (rs == null) {
				return null;
			}
			if (rs.next()) {
				return table2object(table);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * Use the table(TableDescriptor) convert to the Object by the rs(ResultSet).
	 * @param table
	 * @return
	 */
	private T table2object(TableDescriptor table) {
		T result = null;
		try {
			result = (T) entity.newInstance();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
//		Field[] fields = entity.getDeclaredFields();
		Field field = null;
		for (int i = 0; i < table.columns.length; i++) {
			try {
				field = entity.getDeclaredField(table.columns[i].javaField);
			} catch (SecurityException e) {
				e.printStackTrace();
			} catch (NoSuchFieldException e) {
				e.printStackTrace();
			}
			if (null != field) {
				if (!field.isAccessible()) {
					field.setAccessible(true);
				}
			}
			Object value = null;
			try {
				value = rs.getObject(table.columns[i].name);
			} catch (SQLException e) {
				String message = e.getMessage();
				if (message.indexOf("Column")>= 0 && message.indexOf("not found")>= 0) {
					// ignore.
					System.err.println(message);
				} else {
					e.printStackTrace();
				}
			}
			if (null == value && field.getType().isPrimitive()) {
				continue; // ignore the primitive type of the field, when the value is null.
			}
			try {
				
				if ( value instanceof byte[] && 
					  CharSequence.class.isAssignableFrom( field.getType() )) {
					byte[] buf =  ( byte [] ) ( value );
					try {
						value =  new String(buf , 0 , buf.length, "utf-8");
					} catch (UnsupportedEncodingException e) {
						e.printStackTrace();
					}
				}
				// fix the blob to the string.
				field.set(result, value);
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			}
		}
		return result;
	}

	@Override
	public List<?> script( final String script) {
		String result;
		List<String> list = new ArrayList<String>();
		try {
			
			this.pstmt = 
				dbEngine.getConnection()
				.prepareStatement( script );
			rs = pstmt.executeQuery();
			
			while ( rs.next() ) {
				result = rs.getString( 1 );
				list.add( result );
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return list;
	}
}
